
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.CRM.BigCustomerFreightConfiguration;
using BCCommon.TMS.FreightEnums;
using BCCommon.TMS.Order;

namespace BCData.CRM.BigCustomerFreightConfiguration
{
    public class BigcustomerfreightconfigurationData : IBigCustomerFreightConfigurationData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    A.BigCustomerFreightConfigurationId,
		    A.FreightConfigurationId,
		    A.CompanyId,
		    A.AgentRecordId,
		    A.BigCustomerId,
		    A.StartPrice,
		    A.StartMileage,
		    A.MileagePrice,
		    A.CreateTime,
		    A.CreateAdminId,
		    A.CreateAdminName,
		    A.UpdateTime,
		    A.EditAdminId,
		    A.EditAdminName
        FROM
            bigcustomerfreightconfiguration A";


        private readonly string QueryView = @"SELECT {0} 
	                                     FROM freightconfiguration A
	                                       INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId
	                                       LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
	                                       LEFT JOIN city C ON A.CityCode = C.CityCode
	                                       LEFT JOIN county CO ON A.CountyCode = CO.CountyCode
	                                       LEFT JOIN (
	                                       SELECT
		                                        A.vehiclecategoryreferenceId,
		                                        A.VehicleTypeId,
		                                        A.VehicleCategory,
		                                        B.ItemValue AS CategoryValue,
		                                        C.VehicleTypeName
	                                        FROM
		                                        vehiclecategoryreference A
		                                        INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                                        INNER JOIN vehicletype C ON A.VehicleTypeId = C.VehicleTypeId
	                                        ) D ON A.VehicleCategoryReferenceId = D.VehicleCategoryReferenceId
	                                        INNER JOIN bigcustomerfreightconfiguration E ON A.FreightConfigurationId = E.FreightConfigurationId";
        private readonly string QueryViewFiled = @" 
                                                                E.BigCustomerFreightConfigurationId,
                                                                E.FreightConfigurationId,
                                                                E.CompanyId,
                                                                E.AgentRecordId,
                                                                E.BigCustomerId,
                                                                E.StartPrice, 
                                                                E.StartMileage,
                                                                E.MileagePrice,
                                                                E.CreateTime,
                                                                E.CreateAdminId,
                                                                E.CreateAdminName,
                                                                E.UpdateTime,
                                                                E.EditAdminId,
                                                                E.EditAdminName,
 
                                                                A.VehicleCategoryReferenceId,
                                                                A.ProvinceCode,
                                                                A.CityCode,
                                                                A.CountyCode,
                                                                A.VehicleTypeId,  
                                                                A.SourceType,
                                                                A.FreightType,
                                                                C.CityNegotiatedPrice,
                                                                CO.CountyName,
                                                                P.ProvinceName,
                                                                C.CityName,
                                                                B.VehicleTypeName,
                                                                D.VehicleCategoryReferenceId,
                                                                D.CategoryValue";

        #region 构造函数
        public BigcustomerfreightconfigurationData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        public async Task<BigCustomerFreightConfigurationEntity> InsertAsync(BigCustomerFreightConfigurationEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region InsertAllAsync 
        public async Task<int> InsertAllAsync(List<BigCustomerFreightConfigurationEntity> entitys)
        {
            return await this.database.Master.InsertAllAsync(entitys, database.Transaction);
        }
        #endregion

        #region updateAsync
        public async Task<BigCustomerFreightConfigurationEntity> UpdateAsync(BigCustomerFreightConfigurationEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(long bigCustomerFreightConfigurationId)
        {
            string sql = "DELETE FROM bigcustomerfreightconfiguration WHERE BigCustomerFreightConfigurationId = ?bigCustomerFreightConfigurationId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerFreightConfigurationId", bigCustomerFreightConfigurationId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<BigCustomerFreightConfigurationEntity> GetEntityAsync(long bigCustomerFreightConfigurationId)
        {
            string sql = QUERY + " WHERE  A.BigCustomerFreightConfigurationId = ?bigCustomerFreightConfigurationId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerFreightConfigurationId", bigCustomerFreightConfigurationId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerFreightConfigurationEntity>(sql, parameters);
        }
        #endregion

        #region 获取大客户运费配置记录View
        public async Task<BigCustomerFreightConfigurationViewEntity> GetViewEnitiyAsync(long bigCustomerFreightConfigurationId)
        {
            string sql = string.Format(QueryView, QueryViewFiled);
            sql +=  " WHERE  BigCustomerFreightConfigurationId = ?bigCustomerFreightConfigurationId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerFreightConfigurationId", bigCustomerFreightConfigurationId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerFreightConfigurationViewEntity>(sql, parameters);
        }
        #endregion


        #region GetListAsync 查询大客户运费配置记录
        public async Task<(IEnumerable<BigCustomerFreightConfigurationViewEntity>, int)> GetListAsync(int? companyId,long? agentRecordId,long? bigCustomerId,int? vehicleTypeId, int? vehicleCategoryReferenceId, string ProvinceCode, string cityCode, string countyCode, OrderSourceType? sourceType, FreightType? freightType,int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if(companyId.HasValue)
            {
                wheres.Add("CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int64);
            }
            if(agentRecordId.HasValue)
            {
                wheres.Add("AgentRecordId=?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            }
            if(bigCustomerId.HasValue)
            {
                wheres.Add("BigCustomerId=?BigCustomerId");
                parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);
            }
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId);
            }
            if (vehicleCategoryReferenceId.HasValue)
            {
                wheres.Add(" A.VehicleCategoryReferenceId=?VehicleCategoryReferenceId");
                parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId);
            }
            if (!string.IsNullOrWhiteSpace(ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", ProvinceCode);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode);
            }
            if (sourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (freightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", freightType.Value.ToString(), DbType.AnsiString);
            }

            string sql = string.Format(@"{0} {1} LIMIT {2},{3}; {4}  {1}",
                string.Format(QueryView, QueryViewFiled),
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                string.Format(QueryView, "COUNT(1)"));
             
            return await this.database.Slave.QueryPageableListAsync<BigCustomerFreightConfigurationViewEntity>(sql, parameters);
        }

        #endregion

        #region GetEntityAsync
        public async Task<BigCustomerFreightConfigurationEntity> GetEntityAsync(long freightConfigurationId, long bigCustomerId)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            wheres.Add("FreightConfigurationId=?FreightConfigurationId");
            parameters.Add("?FreightConfigurationId", freightConfigurationId, DbType.Int64);
            wheres.Add("BigCustomerId=?BigCustomerId");
            parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);

            string sql = string.Format("{0} {1}", 
                QUERY ,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.database.Slave.QueryEntityAsync<BigCustomerFreightConfigurationEntity>(sql, parameters);
        }
        #endregion
         
    }
}
        //接口
        
        
        
